package com.cxx.purchasecharge.dal.impl;

import java.util.List;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.apache.log4j.Logger;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageImpl;
import org.springframework.data.domain.Pageable;

import com.cxx.purchasecharge.core.model.persistence.Contact;
import com.cxx.purchasecharge.dal.ContactDaoPlus;
import com.cxx.purchasecharge.dal.common.GenericDaoUtils;

public class ContactDaoImpl implements ContactDaoPlus
{
    private static final Logger logger = Logger.getLogger (ContactDaoImpl.class);

    @PersistenceContext
    private EntityManager entityManager;

    @Override
    public List <Contact> findByFuzzyName (String keyWord)
    {
        logger.debug ("Find contact by fuzzy name: " + keyWord);
        String searchKey = (null == keyWord ? "" : keyWord.trim ());
        String sql = "select * from pc_contact c where c.name like ?1 order by convert(c.name USING gbk) COLLATE gbk_chinese_ci asc";
        // String sql =
        // "select c from Contact c where c.name like ?1 order by c.name";
        Query query = entityManager.createNativeQuery (sql, Contact.class);
        query.setParameter (1, searchKey + "%");
        @SuppressWarnings ("unchecked")
        List <Contact> contactList = query.getResultList ();
        return contactList;
    }

    @Override
    public Page <Contact> findByFuzzy (Pageable pageable, String searchKey, long signUser, boolean admin)
    {
        String pageableLog = "offSet:" + pageable.getOffset () + ",pageNumber:" + pageable.getPageNumber ()
                             + ",pageSize:" + pageable.getPageSize () + ",sort:" + pageable.getSort ();
        logger.debug ("Find paging contact by fuzzy, pageable: (" + pageableLog + "), searchkey: " + searchKey);
        String keyWord = (null == searchKey ? "" : searchKey.trim ());
        Page <Contact> contactPage;
        if (!admin && 0 != signUser)
        {
            String selectCountSql = "SELECT count(c) ";
            String fromSql = "FROM Contact c where ";
            String conditionSql = "c.userCreatedBy = ?1 and c.id IN ";
            
            conditionSql += "(select c1.id from Contact c1 where c1.name like ?2 or " 
                    + "c1.customer.shortName like ?3 or " + "c1.mobilePhone like ?4 or "
                    + "c1.qq like ?5 or " + "c1.skype like ?6 or " + "c1.email like ?7 or "
                    + "c1.weiXin like ?8 or " + "c1.wangWang like ?9 or " + "c1.dept like ?10 or "
                    + "c1.concernedBusiness like ?11 or " + "c1.post like ?12 or " + "c1.interest like ?13 or "
                    + "c1.address like ?14 or " + "c1.region like ?15 or " + "c1.type.name like ?16) ";

            Query countQuery = entityManager.createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, signUser);
            countQuery.setParameter (2, keyWord + "%");
            countQuery.setParameter (3, keyWord + "%");
            countQuery.setParameter (4, keyWord + "%");
            countQuery.setParameter (5, keyWord + "%");
            countQuery.setParameter (6, keyWord + "%");
            countQuery.setParameter (7, keyWord + "%");
            countQuery.setParameter (8, keyWord + "%");
            countQuery.setParameter (9, keyWord + "%");
            countQuery.setParameter (10, keyWord + "%");
            countQuery.setParameter (11, keyWord + "%");
            countQuery.setParameter (12, keyWord + "%");
            countQuery.setParameter (13, keyWord + "%");
            countQuery.setParameter (14, keyWord + "%");
            countQuery.setParameter (15, keyWord + "%");
            countQuery.setParameter (16, keyWord + "%");
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());
            
            String querySql = "SELECT c ";
            querySql += fromSql;
            querySql += conditionSql;
            String orderBySql = " ORDER BY ";
            querySql += orderBySql;
            String sortSqlStr = GenericDaoUtils.parseSort (Contact.class, pageable, "c");
            if (!"".endsWith (sortSqlStr))
            {
              querySql += sortSqlStr;
            }
            else
            {
              querySql += "c.name asc";
            }
            Query query = entityManager.createQuery (querySql);
            query.setParameter (1, signUser);
            query.setParameter (2, keyWord + "%");
            query.setParameter (3, keyWord + "%");
            query.setParameter (4, keyWord + "%");
            query.setParameter (5, keyWord + "%");
            query.setParameter (6, keyWord + "%");
            query.setParameter (7, keyWord + "%");
            query.setParameter (8, keyWord + "%");
            query.setParameter (9, keyWord + "%");
            query.setParameter (10, keyWord + "%");
            query.setParameter (11, keyWord + "%");
            query.setParameter (12, keyWord + "%");
            query.setParameter (13, keyWord + "%");
            query.setParameter (14, keyWord + "%");
            query.setParameter (15, keyWord + "%");
            query.setParameter (16, keyWord + "%");
            
            query.setFirstResult (pageable.getOffset ());
            query.setMaxResults (pageable.getPageSize ());
            
            @SuppressWarnings ("unchecked")
            List<Contact> contactList = query.getResultList ();
            contactPage = new PageImpl <Contact> (contactList, pageable, total);
        }
        else 
        {
            String selectCountSql = "SELECT count(c) ";
            String fromSql = "FROM Contact c where ";
            String conditionSql = "c.name like ?1 or " + "c.customer.shortName like ?2 or " + "c.mobilePhone like ?3 or "
                                  + "c.qq like ?4 or " + "c.skype like ?5 or " + "c.email like ?6 or "
                                  + "c.weiXin like ?7 or " + "c.wangWang like ?8 or " + "c.dept like ?9 or "
                                  + "c.concernedBusiness like ?10 or " + "c.post like ?11 or " + "c.interest like ?12 or "
                                  + "c.address like ?13 or " + "c.region like ?14 or " + "c.type.name like ?15 ";

            Query countQuery = entityManager.createQuery (selectCountSql + fromSql + conditionSql);
            countQuery.setParameter (1, keyWord + "%");
            countQuery.setParameter (2, keyWord + "%");
            countQuery.setParameter (3, keyWord + "%");
            countQuery.setParameter (4, keyWord + "%");
            countQuery.setParameter (5, keyWord + "%");
            countQuery.setParameter (6, keyWord + "%");
            countQuery.setParameter (7, keyWord + "%");
            countQuery.setParameter (8, keyWord + "%");
            countQuery.setParameter (9, keyWord + "%");
            countQuery.setParameter (10, keyWord + "%");
            countQuery.setParameter (11, keyWord + "%");
            countQuery.setParameter (12, keyWord + "%");
            countQuery.setParameter (13, keyWord + "%");
            countQuery.setParameter (14, keyWord + "%");
            countQuery.setParameter (15, keyWord + "%");
            int total = Integer.parseInt (countQuery.getResultList ().get (0).toString ());

            String querySql = "SELECT c ";
            querySql += fromSql;
            querySql += conditionSql;
            String orderBySql = " ORDER BY ";
            querySql += orderBySql;
            String sortSqlStr = GenericDaoUtils.parseSort (Contact.class, pageable, "c");
            if (!"".endsWith (sortSqlStr))
            {
                querySql += sortSqlStr;
            }
            else
            {
                querySql += "c.name asc";
            }
            Query query = entityManager.createQuery (querySql);
            query.setParameter (1, keyWord + "%");
            query.setParameter (2, keyWord + "%");
            query.setParameter (3, keyWord + "%");
            query.setParameter (4, keyWord + "%");
            query.setParameter (5, keyWord + "%");
            query.setParameter (6, keyWord + "%");
            query.setParameter (7, keyWord + "%");
            query.setParameter (8, keyWord + "%");
            query.setParameter (9, keyWord + "%");
            query.setParameter (10, keyWord + "%");
            query.setParameter (11, keyWord + "%");
            query.setParameter (12, keyWord + "%");
            query.setParameter (13, keyWord + "%");
            query.setParameter (14, keyWord + "%");
            query.setParameter (15, keyWord + "%");

            query.setFirstResult (pageable.getOffset ());
            query.setMaxResults (pageable.getPageSize ());

            @SuppressWarnings ("unchecked")
            List <Contact> contactList = query.getResultList ();
            contactPage = new PageImpl <Contact> (contactList, pageable, total);
        }
        return contactPage;
    }

    @Override
    public List <Contact> findBySignUserAndSharable (long userCreatedBy, boolean sharable)
    {
        logger.debug ("findBySignUserAndSharable: " + userCreatedBy + " " + sharable);
        String sql = "select * from pc_contact c where c.userCreatedBy = ?1 and c.sharable = ?2 order by convert(c.name USING gbk) COLLATE gbk_chinese_ci asc";
        Query query = entityManager.createNativeQuery (sql, Contact.class);
        query.setParameter (1, userCreatedBy);
        query.setParameter (2, sharable);
        @SuppressWarnings ("unchecked")
        List <Contact> contactList = query.getResultList ();
        return contactList;
    }

}
